package cn.net.vote.util;

import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.util.StringUtils;

import cn.net.vote.data.domain.PageData;


/**
 * 从EXCEL导入到数据库
 * 创建人：Tianxy
 * 创建时间：2017年09月13日
 * @version
 */
public class TemporaryCarExcelRead {

	
	 private static PageData head = new PageData();
	 
	 static{
			head.put("车牌号码(*)","license_plate_no");
			head.put("号牌种类编码(*)","license_plate_type");
			head.put("布控原因(*)","reason");
			head.put("布控状态(*)","ctrl_status");
			head.put("布控警员(*)","ctrl_police_name");
			head.put("布控警员联系方式(*)","ctrl_police_phone");
			head.put("布控民警单位","ctrl_police_unit");
		//	head.put("布控民警管辖单位","ctrl_police_area");
			head.put("布控类型","ctrl_type");
			head.put("车辆类型","car_type");
			head.put("车身颜色","car_color");
			head.put("车辆型号","car_model");
			head.put("车辆品牌","car_brand");
			head.put("VIN码","vin_number");
			head.put("车辆所有人","car_owner");
			head.put("车辆所有人联系方式","car_owner_tel");
	 }
	/**
	 * @param filepath //文件路径
	 * @param filename //文件名
	 * @param startrow //开始行号
	 * @param startcol //开始列号
	 * @param sheetnum //sheet
	 * @return list
	 */
	public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
		List<Object> varList = new ArrayList<Object>();

		try {
			File target = new File(filepath, filename);
			FileInputStream fi = new FileInputStream(target);
			HSSFWorkbook wb = new HSSFWorkbook(fi);
			HSSFSheet sheet = wb.getSheetAt(sheetnum); 					//sheet 从0开始
			int rowNum = sheet.getLastRowNum() + 1; 					//取得最后一行的行号
			HSSFRow rowHead = sheet.getRow(0); 	
			HSSFCell cellHead = null;
			String strHead = "";
			for (int i = startrow; i < rowNum; i++) {					//行循环开始
				
				PageData varpd = new PageData();
				HSSFRow row = sheet.getRow(i); 							//行
				int cellNum = row.getLastCellNum(); 					//每行的最后一个单元格位置

				for (int j = startcol; j < cellNum; j++) {				//列循环开始
					cellHead = rowHead.getCell(Short.parseShort(j + ""));
					
					strHead = cellHead.getStringCellValue();
					HSSFCell cell = row.getCell(Short.parseShort(j + ""));
					String cellValue = null;
					if (null != cell) {
						
						
						switch (cell.getCellType()) { 					// 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
						case 0:
							DecimalFormat df = new DecimalFormat("0");
							cellValue = df.format(cell.getNumericCellValue());
							break;
						case 1:
							cellValue = cell.getStringCellValue();
							break;
						case 2:
							cellValue = cell.getNumericCellValue() + "";
							// cellValue = String.valueOf(cell.getDateCellValue());
							break;
						case 3:
							cellValue = "";
							break;
						case 4:
							cellValue = String.valueOf(cell.getBooleanCellValue());
							break;
						case 5:
							cellValue = String.valueOf(cell.getErrorCellValue());
							break;
						}
					} else {
						cellValue = "";
					}
					
					varpd.put(head.getString(strHead), StringUtils.trimAllWhitespace(cellValue));
					
				}
				varList.add(varpd);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return varList;
	}
	
	public static boolean CheckExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
		List<Object> varList = new ArrayList<Object>();
		DecimalFormat df = new DecimalFormat("0"); 
		boolean result = false;
	
		try {
			File target = new File(filepath, filename);
			FileInputStream fi = new FileInputStream(target);
			HSSFWorkbook wb = new HSSFWorkbook(fi);
			HSSFSheet sheet = wb.getSheetAt(sheetnum); 					//sheet 从0开始
			int rowNum = sheet.getLastRowNum() + 1; 					//取得最后一行的行号

		 
				
				PageData varpd = new PageData();
				HSSFRow row = sheet.getRow(0); 		
				Set<String> titles = new HashSet<String>();//行
		for(Iterator<Cell> it =row.cellIterator();it.hasNext(); ){
			Cell cell  = it.next();
			
			if(cell.getCellType()==HSSFCell.CELL_TYPE_STRING){
				titles.add(cell.getStringCellValue());
			}else if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
				titles.add(df.format(cell.getNumericCellValue() ) );
			}
			
		}

			if(titles.containsAll(head.keySet())){
				result = true;
			} 
			 

		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return result;
	}
}
